package com.sync.platform.service.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.sync.platform.entity.DatabaseConfig;
import com.sync.platform.mapper.DatabaseConfigMapper;
import com.sync.platform.service.DatabaseConfigService;
import com.sync.platform.util.DatabaseUtil;
import com.sync.platform.util.JdbcUrlBuilder;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Database configuration service implementation
 */
@Service
public class DatabaseConfigServiceImpl extends ServiceImpl<DatabaseConfigMapper, DatabaseConfig> implements DatabaseConfigService {

    private static final Logger logger = LoggerFactory.getLogger(DatabaseConfigServiceImpl.class);

    @Override
    public boolean testConnection(DatabaseConfig databaseConfig) {
        try {
            // Generate JDBC URL if not provided
            if (databaseConfig.getUrl() == null || databaseConfig.getUrl().isEmpty()) {
                String url = JdbcUrlBuilder.buildJdbcUrl(
                        databaseConfig.getDbType(),
                        databaseConfig.getHost(),
                        databaseConfig.getPort(),
                        databaseConfig.getDatabaseName(),
                        databaseConfig.getCharset()
                );
                databaseConfig.setUrl(url);
            }

            // Generate driver class name if not provided
            if (databaseConfig.getDriverClass() == null || databaseConfig.getDriverClass().isEmpty()) {
                String driverClass = JdbcUrlBuilder.getDriverClassName(databaseConfig.getDbType());
                databaseConfig.setDriverClass(driverClass);
            }

            return DatabaseUtil.testConnection(
                    databaseConfig.getUrl(),
                    databaseConfig.getDriverClass(),
                    databaseConfig.getUsername(),
                    databaseConfig.getPassword()
            );
        } catch (Exception e) {
            logger.error("Error testing database connection: {}", e.getMessage(), e);
            return false;
        }
    }

    @Override
    public boolean updateStatus(Long id, Integer status) {
        DatabaseConfig databaseConfig = getById(id);
        if (databaseConfig == null) {
            return false;
        }

        databaseConfig.setStatus(status);
        return updateById(databaseConfig);
    }

    @Override
    public List<String> getTables(Long id) {
        DatabaseConfig databaseConfig = getById(id);
        if (databaseConfig == null) {
            logger.error("Database configuration not found for ID: {}", id);
            return new ArrayList<>();
        }

        logger.info("Getting tables for database: {} ({}), host: {}, database name: {}", 
                databaseConfig.getName(), databaseConfig.getDbType(), 
                databaseConfig.getHost(), databaseConfig.getDatabaseName());

        Connection connection = null;
        try {
            // Ensure URL and driver are set
            if (databaseConfig.getUrl() == null || databaseConfig.getUrl().isEmpty()) {
                String url = JdbcUrlBuilder.buildJdbcUrl(
                        databaseConfig.getDbType(),
                        databaseConfig.getHost(),
                        databaseConfig.getPort(),
                        databaseConfig.getDatabaseName(),
                        databaseConfig.getCharset()
                );
                databaseConfig.setUrl(url);
                logger.info("Generated URL for database connection: {}", url);
            }

            if (databaseConfig.getDriverClass() == null || databaseConfig.getDriverClass().isEmpty()) {
                String driverClass = JdbcUrlBuilder.getDriverClassName(databaseConfig.getDbType());
                databaseConfig.setDriverClass(driverClass);
                logger.info("Generated driver class for database connection: {}", driverClass);
            }

            logger.info("Connecting to database with URL: {}, driver: {}", 
                    databaseConfig.getUrl(), databaseConfig.getDriverClass());
                    
            connection = DatabaseUtil.getConnection(
                    databaseConfig.getUrl(),
                    databaseConfig.getDriverClass(),
                    databaseConfig.getUsername(),
                    databaseConfig.getPassword()
            );

            List<String> allTables = DatabaseUtil.getTables(connection);
            logger.info("Initially retrieved {} tables from database ID: {}", allTables.size(), id);
            
            // If we retrieve tables from a database but they don't belong to our database,
            // try to filter tables by database name or common prefix
            if (!allTables.isEmpty() && databaseConfig.getDbType().equalsIgnoreCase("MYSQL")) {
                // Extract only tables specific to this database based on prefix if needed
                String dbName = databaseConfig.getDatabaseName().toLowerCase();
                List<String> filteredTables = new ArrayList<>();
                
                // For MySQL, include tables in the current catalog without any prefixes
                filteredTables.addAll(allTables);
                
                logger.info("Filtered to {} tables for database: {}", filteredTables.size(), dbName);
                return filteredTables;
            }
            
            return allTables;
        } catch (Exception e) {
            logger.error("Failed to get tables for database ID: {}, error: {}", id, e.getMessage(), e);
            return new ArrayList<>();
        } finally {
            DatabaseUtil.closeConnection(connection);
        }
    }

    @Override
    public List<Map<String, String>> getColumns(Long id, String tableName) {
        DatabaseConfig databaseConfig = getById(id);
        if (databaseConfig == null) {
            return new ArrayList<>();
        }

        Connection connection = null;
        try {
            // Ensure URL and driver are set
            if (databaseConfig.getUrl() == null || databaseConfig.getUrl().isEmpty()) {
                String url = JdbcUrlBuilder.buildJdbcUrl(
                        databaseConfig.getDbType(),
                        databaseConfig.getHost(),
                        databaseConfig.getPort(),
                        databaseConfig.getDatabaseName(),
                        databaseConfig.getCharset()
                );
                databaseConfig.setUrl(url);
            }

            if (databaseConfig.getDriverClass() == null || databaseConfig.getDriverClass().isEmpty()) {
                String driverClass = JdbcUrlBuilder.getDriverClassName(databaseConfig.getDbType());
                databaseConfig.setDriverClass(driverClass);
            }

            connection = DatabaseUtil.getConnection(
                    databaseConfig.getUrl(),
                    databaseConfig.getDriverClass(),
                    databaseConfig.getUsername(),
                    databaseConfig.getPassword()
            );

            return DatabaseUtil.getColumns(connection, tableName);
        } catch (Exception e) {
            logger.error("Get columns failed: {}", e.getMessage(), e);
            return new ArrayList<>();
        } finally {
            DatabaseUtil.closeConnection(connection);
        }
    }

    @Override
    public List<Map<String, Object>> executeQuery(Long id, String sql) {
        DatabaseConfig databaseConfig = getById(id);
        if (databaseConfig == null) {
            return new ArrayList<>();
        }

        Connection connection = null;
        try {
            // Ensure URL and driver are set
            if (databaseConfig.getUrl() == null || databaseConfig.getUrl().isEmpty()) {
                String url = JdbcUrlBuilder.buildJdbcUrl(
                        databaseConfig.getDbType(),
                        databaseConfig.getHost(),
                        databaseConfig.getPort(),
                        databaseConfig.getDatabaseName(),
                        databaseConfig.getCharset()
                );
                databaseConfig.setUrl(url);
            }

            if (databaseConfig.getDriverClass() == null || databaseConfig.getDriverClass().isEmpty()) {
                String driverClass = JdbcUrlBuilder.getDriverClassName(databaseConfig.getDbType());
                databaseConfig.setDriverClass(driverClass);
            }

            connection = DatabaseUtil.getConnection(
                    databaseConfig.getUrl(),
                    databaseConfig.getDriverClass(),
                    databaseConfig.getUsername(),
                    databaseConfig.getPassword()
            );

            return DatabaseUtil.executeQuery(connection, sql);
        } catch (Exception e) {
            logger.error("Execute query failed: {}", e.getMessage(), e);
            return new ArrayList<>();
        } finally {
            DatabaseUtil.closeConnection(connection);
        }
    }

    @Override
    public DatabaseConfig getDatabaseConfig(Long id) {
        return getById(id);
    }

    @Override
    public int countDatabases() {
        return (int) count();
    }
}
